#!/usr/bin/python3
# -*- coding:utf-8 -*-
""""
@Author: lijk34925
@Time: 2023/4/20 20:01
@File: ActSqlMethod.py
"""
import os
import re

from jsonpath import jsonpath

from AmlScripts import blade_variable
from BladeFerm.Lib.ClassSelenium import SeleniumError
from BladeFerm.Lib.Common import logger
from BladeFerm.utils.DiffJson import DiffJson
from BladeFerm.databases import DB_APP
from BladeFerm.databases.DealSql.MakeSql import MakeSql
from BladeFerm.databases.SqlDealApp import parse_sql

DJA = DiffJson()
DJA.set_compare_kwargs({'ignore_string_type_changes': True, 'ignore_numeric_type_changes': True})
MS = MakeSql()
# -------------- 脚本准备工作完成 -------------------------

def update_sys_init_log_task_status(input_data=None, ui_expect=None):
    sql_lines = ["update hspbs.sys_init_log set task_status = '0' "
                 "where occur_date in(select init_date from hspbs.sys_init_date where product_code = 'aml') "
                 "and task_id = '1020003';"]
    logger.info(f'SQL语句:{sql_lines}')
    sql_response = DB_APP.get_run_sql_result(sql_lines)
    return sql_response


def act_amlrc_init_ruleset(input_data=None, ui_expect=None):
    rules_no = input_data.get('rules_no')
    sql_1 = f"delete from hsamlrc.amlrc_cfg_rulescoreinfo where rules_no = '{rules_no}';"
    sql_2 = f"delete from hsamlrc.amlrc_cfg_rulescore where rules_no = '{rules_no}';"
    sql_3 = "{call hsamlrc.p_amlrc_init_ruleset}"
    sql_lines = [sql_1, sql_2, sql_3]
    logger.info(f'SQL语句:{sql_lines}')
    result = DB_APP.get_run_sql_result(sql_lines)
    return result


def diff_actual_and_expect_value(key, value, target_list):
    """
    比较期望结果和时间结果
    """
    # 获取 比较方式
    if re.match(r're\|', value, re.I):
        diff_mode = 're'
        value = value[3:]
    elif re.match(r'^(>|>=|<|<=)\|(.*?)$', value):
        res_m = re.match(r'^(>|>=|<|<=)\|(.*?)$', value)
        diff_mode = res_m.group(1)
        value = res_m.group(2)
    else:
        diff_mode = 'json'
    logger.info(f'diff_mode: {diff_mode}, value: {value}')
    # 获取 期望值
    except_res_raw = {key: value}
    if DJA.is_number(value):
        except_res = {key: float(value)}
    elif value.upper() == 'NONE':
        except_res = {key: None}
    else:
        except_res = {key: value}
    # 简化筛选值，提高断言效率
    target_list_raw = []
    target_list_second = list(set(target_list))
    if len(target_list_second) == 1:
        target_list_raw = target_list_second
    else:
        target_list_raw.extend(target_list)
        logger.warning(f'实际断言结果超过1条，或存在与期望不符的数据！')
    # 遍历 实际值 与 期望值进行比较
    for i, act_value in enumerate(target_list_raw):
        key_remote = f'varc_{i}_{key}'
        blade_variable.set_variable(key_remote, act_value)
        logger.debug(f'设置实际响应结果：{key_remote}={act_value}')
        if diff_mode == 'json':
            # 判断相等，用 json 对比方法，主要是解决数据类型不一致的问题
            act_value_new = float(act_value) if act_value and DJA.is_number(act_value) else act_value
            check_act_response = {key: act_value_new}
            act_response = {key: act_value}
            check_json_result = DJA.compare_json(except_res, check_act_response)
        elif diff_mode == 're':
            # 正则匹配判断
            act_response = {key: act_value}
            res_pattern = re.search(value, act_value, re.I)
            check_json_result = {'success': 1, 'message': '检查没有差异'} if res_pattern else {'success': 0}
        else:
            # 大于，小于，大于等于，小于等于 的数值大小判断
            if DJA.is_number(act_value) and DJA.is_number(value):
                express_diff = f'{float(act_value)} {diff_mode} {float(value)}'
                express_diff_result = eval(express_diff)
                if express_diff_result:
                    check_json_result = {'success': 1, 'message': '检查没有差异'}
                else:
                    check_json_result = {'success': 0, 'message': f'检查不符合期望表达式：{express_diff}'}
                act_response = {key: act_value}
            else:
                act_response = {key: act_value}
                check_json_result = {'success': 0, 'message': f'实际值：{act_value} 和期望值: {value} 是非数值，不具备比较条件！'}
        logger.info(f'except_res:　{except_res_raw}, act_response: {act_response}, '
                    f'check_json_result: {check_json_result}')
        success_flag = check_json_result.get('success', 0)
        if not success_flag:
            error = f'检查 {key}的期望结果: {value} 与测试第{i}组的实际结果 {act_value} 不一致'
            logger.error(error)
            raise SeleniumError(error)


def check_response(input_data: dict=None, ui_expect: dict=None):
    """
    断言结果，通过 jsonpath 找出对应的测试值，与期望值进行比较
           或 比较 sql查询的期望结果 与 测试sql查询的结果 之间结果，实现2份json数据进行比较
    Args:
        input_data:
            - 是否内部断言: 执行脚本断言
            - act_sql_response：获取时间响应的数据
        ui_expect:
            - k：v     # v 若以 ‘|re’ 结束时，采用正则匹配方式进行断言
            - SQL: 'select * from dual;'
    Return

    """
    is_check = bool(input_data.get('是否内部断言', True))
    response = input_data.pop('act_sql_response', None)
    ERROR_INFO = jsonpath(response, "$..ERROR_INFO")
    if ERROR_INFO and isinstance(ERROR_INFO, list):
        raise SeleniumError(f'运行存在失败信息：{ERROR_INFO}')
    if is_check and response:
        ui_expect_result = dict()
        for key, value in ui_expect.items():
            res_pattern = re.search(r'.*? as (.*?)$', key, re.I)
            if res_pattern:
                key = res_pattern.group(1)
            logger.info(f'assert key : {key}, value : {value}')
            if key.upper() in ('ERROR_KEYWORD', 'SUCCESS') or not value:
                logger.warning(f'skip assert key : {key}, value : {value}')
                continue
            if key.upper().startswith('SQL'):
                sql_texts = get_sql_text(input_data, ui_expect)
                except_sql_response = DB_APP.get_run_sql_result(sql_texts)
                check_json_result = DJA.compare_json(except_sql_response, response)
                success_flag = check_json_result.get('success', 0)
                if not success_flag:
                    error = f'检查 {key} 执行 {value} 的期望结果，与测试的实际响应结果 {response} 不一致'
                    logger.error(error)
                    raise SeleniumError(error)
                else:
                    ui_expect_result[key] = value
            elif key.lower() in ('result', 'resp_status_code'):
                continue
            else:
                exp_js = f"$..{key.upper()}"
                target_list = jsonpath(response, exp_js)
                logger.info(f'json path : {exp_js} , result : {target_list}')
                ui_expect_result[key] = value
                if target_list and isinstance(target_list, list):
                    diff_actual_and_expect_value(key, value, target_list)
                else:
                    error = f'比较结果： {key} 获取实际测试值失败，请检查!'
                    logger.error(error)
                    raise SeleniumError(error)
        logger.info(f'断言检查通过！检查项：{ui_expect_result}')
        return True
    else:
        logger.info(f'尚未进行断言!因为 是否内部断言: {is_check} 或 实际响应结果为 ：{response}')
        blade_variable.set_variable('varc_check_result', None)
        return None

def splice_sql_method(input_data=None, ui_expect=None):
    """
    根据入参，拼接sql
    Args:
        input_data:
            * table_name: 表名称, 必填参数
            - sql_json_path： insert所需的初始数据的路径.非必填参数，如果不填，根据表名读取
            * sql_type: sql的类型，eg：insert，Delete, UPDATE，select
            * key  : 字段名称和字段值，用于更新insert ... into，update ... where之间的值
            * key:,:key,key|,|key : where条件的要填的值，字段前或后，添加 | 或 ：进行区分
            - logic：where条件字段的拼接关系，and 或 or；缺省值：and
        ui_expect:
            - 任意参数，只期望该方法单独被调用，将参数返回给平台，其他步骤可以获取

    Returns:

    """
    def input_data_deal(data: dict, data_exp: dict):
        """
        数据预处理，
        Args:
            data:
            data_exp:

        Returns:

        """
        # 场景1： 去掉 error_keyword
        if isinstance(data_exp, dict):
            data_exp.pop('error_keyword', None)
            data_exp.pop('success', None)
        # 场景2： insert 添加 初始文件路径
        sql_type = input_data.get('sql_type', '')
        if sql_type.upper() == 'INSERT':
            sql_json_path = input_data.get('sql_json_path', None)
            sql_json_path = get_file_path(sql_json_path)
            input_data['sql_json_path'] = sql_json_path
        # 场景3： 从input_data中筛选出期望，更新到ui_expect；若是存在期望，设置为 select_keys
        table_name = input_data.get('table_name', 'dual')
        desc_list = DB_APP.dba.get_description(table_name)
        origin_table_keys = jsonpath(desc_list, '$..field_name')
        logger.info(f'origin_table_keys : {origin_table_keys}')
        common_files = ['table_name', 'sql_type', 'sql_json_path']
        new_input_dict = dict()
        table_params = dict()
        common_params = dict()
        case_params = dict()
        expect_dict = dict()
        for key, value in data.items():
            if key in common_files:              # 提取 表名，sql语句类型，sql初始数据文件路径的键值对
                table_params[key] = value
            elif key.upper() in origin_table_keys:
                common_params[key] = value
            elif key.endswith(":") or key.startswith(":"):     # 提取出 where 条件的键值对
                real_key = key.strip(':')
                if real_key.upper() in origin_table_keys:
                    case_params[real_key] = value
            elif re.search(r'expect', key, re.I) and isinstance(value, dict):   # 提取期望断言键值对，从中提取出 select_keys
                for k_exp, v_exp in value.items():
                    if re.search(' as ', k_exp, re.I):
                        expect_dict[k_exp] = v_exp
                    elif k_exp.upper() in origin_table_keys:
                        expect_dict[k_exp] = v_exp
                    else:
                        pass   # 其他字段不管
            else:
                if re.search(' as ', key, re.I):
                    common_params[key] = value
                else:
                    pass  # 其他字段丢弃，不要了
        # select 语句处理期望和拼接键名
        if sql_type.upper() == 'SELECT' and expect_dict:
            data_exp.update(expect_dict)
            new_input_dict['select_keys'] = ', '.join(list(expect_dict.keys()))
        elif sql_type.upper() == 'SELECT' and common_params:
            data_exp.update(common_params)
            new_input_dict['select_keys'] = ', '.join(list(common_params.keys()))
        elif sql_type.upper() == 'SELECT' and not expect_dict and not common_params:
            new_input_dict['select_keys'] = '*'
        else:
            pass
        new_input_dict['table_params'] = table_params
        new_input_dict['common_params'] = common_params
        new_input_dict['case_params'] = case_params
        logger.info(f'input_data :　{new_input_dict}')
        logger.info(f'ui_expect :　{data_exp}')
        if re.match(r'select', sql_type, re.I) and len(list(data_exp.keys())) == 0:
            logger.error(f'select 语句没有给出要断言的字段，请检查是否关联EXPECT(Object)字段是否有关联期望？否则你可能会断言个寂寞！')
        return new_input_dict, data_exp
    # 主程序：拼接生成sql
    input_data, ui_expect = input_data_deal(input_data, ui_expect)
    MS.ui_expect = ui_expect    # 记录一下更新后的 期望值
    sql_text = MS.splice_sql(**input_data)
    if not sql_text:
        error = '拼接sql失败，检查入参是否正确？'
        logger.error(error)
        raise SeleniumError(error)
    if ui_expect:
        blade_variable.set_variable('splice_sql_text', sql_text)
    return sql_text


def get_sql_text(input_data: dict, ui_expect: dict) -> list:
    """
    读取sql文件 或 读取sql语句
    Args:
        input_data:
        ui_expect:

    Returns:

    """
    contents = ""
    # 场景1：从文件读取sql
    file_path = input_data.pop('文件名', None)
    file_path = get_file_path(fileName=file_path) if file_path else file_path
    if file_path:
        encoding = input_data.pop('文件编码', 'utf-8')
        with open(file_path, 'r', encoding=encoding) as file:
            lines = file.readlines()
        contents += ''.join([line for line in lines if not line.startswith('--')])
    # 场景2：从输入参数读取sql
    sql_text : str = input_data.pop('SQL语句', '')
    if sql_text:
        if sql_text and not sql_text.endswith(';'):
            sql_text += ';'
        contents += sql_text
    # 场景3：根据输入参数，自主拼接sql简单语句（where条件不含嵌套），支持拼接insert，select，update，delete语句
    if input_data.get('table_name', None) is not None and input_data.get('sql_type', None) is not None:
        contents += splice_sql_method(input_data, ui_expect)
    # 场景4：SQL的取值中是否存在jinjia2的表达式？如果有替换变量
    if contents and re.search(r"{{.*?}}", contents):
        if re.search(r"cal_before_date", contents):
            sql_lines = "select INIT_DATE FROM HSPBS.SYS_INIT_DATE WHERE product_code = 'aml';"
            sql_res = DB_APP.get_run_sql_result([sql_lines])
            ini_date = sql_res.get(0)[0].get("INIT_DATE")            # {0: [{'INIT_DATE': 20240819}]}
            MS.set_aml_int_date(str(ini_date))                       # 设置系统交易日进行计算  2024-09-01修改
        contents = MS.sql_update_field(contents, input_data)
    # 拆分sql,应该不会有select
    sql_pattern = re.compile(r'((SELECT|UPDATE|DELETE|INSERT|CREATE|ALTER|DROP|COMMIT).*?);(\s+)?$',
                             re.DOTALL | re.IGNORECASE | re.MULTILINE)
    sql_list_res = sql_pattern.findall(contents)
    if sql_list_res:
        # 新增去除 insert语句的time相关字段
        sql_list = [parse_sql(unit[0].strip()) for unit in sql_list_res]
        logger.info(f'sql_list: {sql_list}')
        return sql_list
    else:
        error = f'获取SQL语句为空，支持读取文件 ["文件名": "文件全路径"] 或 读取SQL语句 ["SQL语句": "select * from dual;"]'
        logger.error(error)
        raise SeleniumError(error)


def table_value_effective(field, value, dec_list):
    """
    值的有效性检查
    @DealSql field: DB_APP.dba.get_description() 函数的返回结果
    @DealSql field: DB_APP.dba.get_description() 函数的返回结果
    """
    res = jsonpath(dec_list, f'$.[?(@.field_name=="{field.upper()}")]')
    if res:
        cur_field_info = res[0]
        if len(value) > cur_field_info.get('display_size'):
            err = f'字段 {field} 的值 {value} 的长度超出数据库限制！'
            logger.error(err)
            raise SeleniumError(err)
        if not cur_field_info.get('is_null_able'):
            if not value:
                err = f'字段 {field} 的值 {value} 不能为空！'
                logger.error(err)
                raise SeleniumError(err)
        return True
    else:
        return False


def get_file_path(fileName):
    fileName = fileName.strip()
    CurrentPath = blade_variable.get_variable('varu_CurrentPath')
    # 读取本地脚本路径
    if CurrentPath and CurrentPath == 'AGENT_EMPTY':
        return fileName
    # 根据文件名称搜索执行机环境下的脚本路径，脚本不能有重名的
    elif CurrentPath and "标准数据" not in fileName:
        path = CurrentPath + os.sep + '数据预埋' if CurrentPath and CurrentPath != 'AGENT_EMPTY' else ''
        for root, dirs, files in os.walk(path):
            for file in files:
                file = os.path.join(root, file)
                if fileName in file:
                    logger.info(f'文件路径：{file}')
                    return file
    else:
        return fileName
